/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package kontrol;

import Master.GolonganDarah;
import Master.JenisKelamin;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JOptionPane;
import Model.KK;
import Model.Pasien;
import Master.Penyakit;
import Model.Rekam;
import Master.Tindakan;
import Koneksi.Koneksi;

/**
 *
 * @author BIMO
 */
public class kontrol {

    ResultSet rs = null;
    PreparedStatement pst = null;
    private Connection conn;

    public kontrol(Connection koneksi) {
        this.conn = koneksi;
    }

    public static kontrol getkoneksi() throws SQLException {
        kontrol kon = new kontrol(Koneksi.ConnectDb());

        return kon;
    }

    public void tambahKK(KK kk) throws SQLException {
        int no = kk.getNoKK();
        String nama = kk.getNamaKK();
        String alamat = kk.getAlamat();

        String sql = "INSERT INTO kk (noKK,namaKK,alamat)"
                + "VALUES ("
                + "'" + no + "','" + nama + "','" + alamat + "'"
                + ")";
        Statement stat = conn.createStatement();
        stat.executeUpdate(sql);

    }

    public void tambahPasien(Pasien pasien) throws SQLException {

        String noIndex = pasien.getNoIndex();
        String namaPasien = pasien.getNamaPasien();
        String pekerjaan = pasien.getPekerjaan();
        String agama = pasien.getAgama();
        String tglLahir = pasien.getTanggalLahir();

        int idJK = pasien.getJenisKelamin().getIdJenisKelamin();
        String idGD = pasien.getGolonganDarah().getIdGolonganDarah();
        int noKK = pasien.getNoKK().getNoKK();



        String sql = "INSERT INTO pasien (noIndex,namaPasien,Pekerjaan,agama,noKK,tglLahir,idJK,idGD) VALUES "
                + "('" + noKK + "-" + noIndex + "','" + namaPasien + "','" + pekerjaan + "','" + agama + "'," + noKK + ","
                + "'" + tglLahir + "'," + idJK + ",'" + idGD + "')";


        Statement stat = conn.createStatement();
        stat.executeUpdate(sql);


    }

    public List<JenisKelamin> lihatSeluruhKategori() throws SQLException {
        PreparedStatement statement = null;
        ResultSet result = null;

        conn.setAutoCommit(false);
        statement = conn.prepareStatement("select idJK, JenisKelamin from JenisKelamin"
                + " order by idJK");
        result = statement.executeQuery();
        List<JenisKelamin> kategoris = new ArrayList<JenisKelamin>();
        while (result.next()) {
            JenisKelamin kategori = new JenisKelamin();
            kategori.setIdJenisKelamin(Integer.parseInt(result.getString("idJK")));
            kategori.setJenisKelamin(result.getString("JenisKelamin"));
            kategoris.add(kategori);
        }
        conn.commit();

        return kategoris;

    }

    public List<Pasien> lihatTable(int noKK) throws SQLException {
        PreparedStatement statement = null;
        ResultSet result = null;

        conn.setAutoCommit(false);
        statement = conn.prepareStatement("select * from pasien where noKK=" + noKK);
        result = statement.executeQuery();
        List<Pasien> kategoris = new ArrayList<Pasien>();
        while (result.next()) {
            Pasien kategori = new Pasien();
            kategori.setNoIndex(result.getString("noIndex"));
            kategori.setNamaPasien(result.getString("namaPasien"));
            kategoris.add(kategori);
        }
        conn.commit();
        return kategoris;

    }

    public List<Rekam> lihatRekamUmum(String noIndex) throws SQLException {
        PreparedStatement statement = null;
        ResultSet result = null;

        conn.setAutoCommit(false);
        statement = conn.prepareStatement(" SELECT a.tanggalBerkunjung,a.noIndex,"
                + " a.keluhan,a.nodtd, b.golonganSebabSakit FROM rekamumum a, penyakit b "
                + "WHERE a.noindex='" + noIndex + "' AND a.noDTD=b.noDTD;");

        result = statement.executeQuery();
        List<Rekam> barangs = new ArrayList<Rekam>();
        while (result.next()) {
            Rekam rekam = new Rekam();
            rekam.setKeluhan(result.getString("keluhan"));
            rekam.setTglBerkunjung(result.getString("tanggalBerkunjung"));

            Penyakit pen = new Penyakit();
            pen.setGolSebabSakit(result.getString("golonganSebabsakit"));
            pen.setNoDTD(result.getString("nodtd"));
            rekam.setIdPenyakit(pen);
            barangs.add(rekam);
        }
        conn.commit();
        return barangs;

    }

    public void cariKK(KK br) throws SQLException {
        Statement statement = null;
        ResultSet result = null;

        try {
            statement = conn.createStatement();
            String sql = "select noKK, namaKK,alamat from KK where noKK=" + br.getNoKK() + "";

            result = statement.executeQuery(sql);
            while (result.next()) {

                br.setNoKK(Integer.parseInt(result.getString("noKK")));
                br.setAlamat(result.getString("alamat"));
                br.setNamaKK(result.getString("namaKK"));
            }



        } catch (SQLException ex) {
            Logger.getLogger(kontrol.class.getName()).log(Level.SEVERE, null, ex);

        }
    }

    public void cariPasien(Pasien br) throws SQLException {
        Statement statement = null;
        ResultSet result = null;

        try {
            statement = conn.createStatement();
            String sql = "SELECT a.namaPasien,a.agama,a.idJK,a.pekerjaan,a.idGD,"
                    + "a.tglLahir, c.jeniskelamin, a.noKK,b.namaKK,b.alamat FROM "
                    + "pasien a,kk b,jeniskelamin c  WHERE a.noKK=b.noKK AND"
                    + " a.noIndex='"+br.getNoIndex()+"' AND a.`idJK`=c.`idJK`";

            result = statement.executeQuery(sql);
            while (result.next()) {
                GolonganDarah gd = new GolonganDarah();
                JenisKelamin jk = new JenisKelamin();

                br.setAgama(result.getString("agama"));

                gd.setIdGolonganDarah(result.getString("idGD"));

                br.setGolonganDarah(gd);
                jk.setIdJenisKelamin(Integer.parseInt(result.getString("idJK")));
                jk.setJenisKelamin(result.getString("JenisKelamin"));
                br.setJenisKelamin(jk);
                br.setTanggalLahir(result.getString("tglLahir"));
                br.setNamaPasien(result.getString("namaPasien"));
                br.setPekerjaan(result.getString("pekerjaan"));
                KK kk = new KK();
                kk.setNoKK(Integer.parseInt(result.getString("noKK")));
                kk.setNamaKK(result.getString("namaKK"));
                kk.setAlamat(result.getString("alamat"));
                br.setNoKK(kk);

            }



        } catch (SQLException ex) {
            Logger.getLogger(kontrol.class.getName()).log(Level.SEVERE, null, ex);

        }
    }

    public void updatePasien(Pasien pasien) throws SQLException {
        PreparedStatement statement = null;

        conn.setAutoCommit(false);
        String noIndex = pasien.getNoIndex();
        String namaPasien = pasien.getNamaPasien();
        String pekerjaan = pasien.getPekerjaan();
        String agama = pasien.getAgama();
        String tglLahir = pasien.getTanggalLahir();
        int idJK = pasien.getJenisKelamin().getIdJenisKelamin();
        String idGD = pasien.getGolonganDarah().getIdGolonganDarah();
        String sql = "UPDATE pasien SET namaPasien='" + namaPasien + "', "
                + "pekerjaan='" + pekerjaan + "',agama='" + agama + "',idJK=" + idJK + ","
                + "idGD='" + idGD + "',tglLahir='" + tglLahir + "' WHERE noIndex='" + noIndex + "';";
        statement = conn.prepareStatement(sql);
//        statement.setString(1, kategori.getNamaKategori());
//        statement.setString(2, kategori.getKodeKategori());
        statement.executeUpdate();
        conn.commit();
    }

    public void tambahKeluhanGigi(Rekam rekam) throws SQLException {

//       String tglKunjung = rekam.getTglBerkunjung();
        String noIndex = rekam.getNoIndex().getNoIndex();
        String keluhan = rekam.getKeluhan();
//       String penyakit = rekam.getIdPenyakit().getNoDTD();

        String sql = "INSERT INTO rekamgigi (keluhan,noIndex) VALUES ('" + keluhan + "','" + noIndex + "')";
        pst = conn.prepareStatement(sql);
        pst.execute();


    }

    public void tambahKeluhanKIA(Rekam rekam) throws SQLException {

//       String tglKunjung = rekam.getTglBerkunjung();
        String noIndex = rekam.getNoIndex().getNoIndex();
        String keluhan = rekam.getKeluhan();
//       String penyakit = rekam.getIdPenyakit().getNoDTD();

        String sql = "INSERT INTO rekamkia (keluhan,noIndex) VALUES ('" + keluhan + "','" + noIndex + "')";
        pst = conn.prepareStatement(sql);
        pst.execute();


    }

    public void tambahKeluhanUmum(Rekam rekam) throws SQLException {

        String tgl = rekam.getTglBerkunjung();
        String noIndex = rekam.getNoIndex().getNoIndex();
        String keluhan = rekam.getKeluhan();
//       String penyakit = rekam.getIdPenyakit().getNoDTD();

        String sql = "INSERT INTO rekamumum (keluhan,noIndex,tanggalBerkunjung) VALUES ('" + keluhan + "','" + noIndex + "','" + tgl + "')";
        pst = conn.prepareStatement(sql);
        pst.execute();


    }

    public List<Rekam> lihatPengunjung(String tgl) throws SQLException {
        PreparedStatement statement = null;
        ResultSet result = null;

        conn.setAutoCommit(false);
        statement = conn.prepareStatement("select a.noIndex,a.keluhan,b.namaPasien from "
                + "rekamUmum a, Pasien b where a.noIndex=b.noIndex and tanggalBerkunjung='" + tgl + "';");

        result = statement.executeQuery();
        List<Rekam> barangs = new ArrayList<Rekam>();
        while (result.next()) {
            Rekam rekam = new Rekam();
            rekam.setKeluhan(result.getString("keluhan"));

            Pasien pas = new Pasien();

            String nama = result.getString("namapasien");
            String noIndex = result.getString("noIndex");
            pas.setNamaPasien(nama);
            pas.setNoIndex(noIndex);
            rekam.setNoIndex(pas);
            barangs.add(rekam);
        }
        conn.commit();
        return barangs;

    }
    
    public List<Rekam> lihatPengunjung1(String tgl) throws SQLException {
        PreparedStatement statement = null;
        ResultSet result = null;

        conn.setAutoCommit(false);
        statement = conn.prepareStatement("select a.noIndex,a.keluhan,b.namaPasien from "
                + "rekamKia a, Pasien b where a.noIndex=b.noIndex and tanggalBerkunjung='" + tgl + "';");

        result = statement.executeQuery();
        List<Rekam> barangs = new ArrayList<Rekam>();
        while (result.next()) {
            Rekam rekam = new Rekam();
            rekam.setKeluhan(result.getString("keluhan"));

            Pasien pas = new Pasien();

            String nama = result.getString("namapasien");
            String noIndex = result.getString("noIndex");
            pas.setNamaPasien(nama);
            pas.setNoIndex(noIndex);
            rekam.setNoIndex(pas);
            barangs.add(rekam);
        }
        conn.commit();
        return barangs;

    }
    
    public List<Rekam> lihatPengunjung2(String tgl) throws SQLException {
        PreparedStatement statement = null;
        ResultSet result = null;

        conn.setAutoCommit(false);
        statement = conn.prepareStatement("select a.noIndex,a.keluhan,b.namaPasien from "
                + "rekamGigi a, Pasien b where a.noIndex=b.noIndex and tanggalBerkunjung='" + tgl + "';");

        result = statement.executeQuery();
        List<Rekam> barangs = new ArrayList<Rekam>();
        while (result.next()) {
            Rekam rekam = new Rekam();
            rekam.setKeluhan(result.getString("keluhan"));

            Pasien pas = new Pasien();

            String nama = result.getString("namapasien");
            String noIndex = result.getString("noIndex");
            pas.setNamaPasien(nama);
            pas.setNoIndex(noIndex);
            rekam.setNoIndex(pas);
            barangs.add(rekam);
        }
        conn.commit();
        return barangs;

    }

    public void hitungUmur(Pasien pas) throws SQLException {
        Statement statement = null;
        ResultSet result = null;
        String noIndex = pas.getNoIndex();
        try {
            statement = conn.createStatement();
            String sql = "SELECT namaPasien, tglLahir, CURDATE(),TIMESTAMPDIFF(day,tglLahir,CURDATE()) AS age FROM pasien where noIndex='" + noIndex + "'";

            result = statement.executeQuery(sql);
            while (result.next()) {




                pas.setUmur(Integer.parseInt(result.getString("age")));


            }



        } catch (SQLException ex) {
            Logger.getLogger(kontrol.class.getName()).log(Level.SEVERE, null, ex);

        }
    }
}
